
/****** Object:  Stored Procedure [dbo].mp_Planes_Delete    Script Date: Thursday, August 16, 2012 ******/
if exists (select * from [dbo].sysobjects where id = object_id(N'[dbo].[mp_Planes_Delete]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[mp_Planes_Delete]
GO

/****** Object:  Stored Procedure [dbo].mp_Planes_SelectOne    Script Date: Thursday, August 16, 2012 ******/
if exists (select * from [dbo].sysobjects where id = object_id(N'[dbo].[mp_Planes_SelectOne]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[mp_Planes_SelectOne]
GO

/****** Object:  Stored Procedure [dbo].mp_Planes_GetCount    Script Date: Thursday, August 16, 2012 ******/
if exists (select * from [dbo].sysobjects where id = object_id(N'[dbo].[mp_Planes_GetCount]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[mp_Planes_GetCount]
GO

	
/****** Object:  Stored Procedure [dbo].mp_Planes_SelectAll    Script Date: Thursday, August 16, 2012 ******/
if exists (select * from [dbo].sysobjects where id = object_id(N'[dbo].[mp_Planes_SelectAll]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[mp_Planes_SelectAll]
GO

/****** Object:  Stored Procedure [dbo].mp_Planes_Insert    Script Date: Thursday, August 16, 2012 ******/
if exists (select * from [dbo].sysobjects where id = object_id(N'[dbo].[mp_Planes_Insert]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[mp_Planes_Insert]
GO

/****** Object:  Stored Procedure [dbo].mp_Planes_Update    Script Date: Thursday, August 16, 2012 ******/
if exists (select * from [dbo].sysobjects where id = object_id(N'[dbo].[mp_Planes_Update]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[mp_Planes_Update]
GO


SET QUOTED_IDENTIFIER ON 
GO


CREATE PROCEDURE [dbo].mp_Planes_Delete

/*
Author:   			HungNM
Created: 			16-08-2012
Last Modified: 		16-08-2012
*/

@Id bigint

AS

DELETE FROM [dbo].[Planes]
WHERE
	[Id] = @Id
GO


SET QUOTED_IDENTIFIER OFF 
GO



SET QUOTED_IDENTIFIER ON 
GO



CREATE PROCEDURE [dbo].mp_Planes_GetCount

/*
Author:   			HungNM
Created: 			16-08-2012
Last Modified: 		16-08-2012
*/

AS

SELECT COUNT(*) FROM [dbo].[Planes]

GO


SET QUOTED_IDENTIFIER OFF 
GO



SET QUOTED_IDENTIFIER ON 
GO


CREATE PROCEDURE [dbo].mp_Planes_SelectOne

/*
Author:   			HungNM
Created: 			16-08-2012
Last Modified: 		16-08-2012
*/

@Id bigint

AS


SELECT
		[Id],
		[PlaneId],
		[PlaneName],
		[CompanyId],
		[IsDeleted],
		[CreatedDate],
		[LastUpdate]
		
FROM
		[dbo].[Planes]
		
WHERE
		[Id] = @Id

GO



SET QUOTED_IDENTIFIER OFF 
GO







SET QUOTED_IDENTIFIER ON 
GO


CREATE PROCEDURE [dbo].mp_Planes_SelectAll

/*
Author:   			HungNM
Created: 			16-08-2012
Last Modified: 		16-08-2012
*/

AS


SELECT
		[Id],
		[PlaneId],
		[PlaneName],
		[CompanyId],
		[IsDeleted],
		[CreatedDate],
		[LastUpdate]
		
FROM
		[dbo].[Planes]

GO



SET QUOTED_IDENTIFIER OFF 
GO







SET QUOTED_IDENTIFIER ON 
GO


CREATE PROCEDURE [dbo].mp_Planes_Search

/*
Author:   			HungNM
Created: 			16-08-2012
Last Modified: 		16-08-2012
*/

@SearchText nvarchar(200),
@IsDeleted bit

AS


SELECT
		[Planes].[Id],
		[Planes].[PlaneId],
		[Planes].[PlaneName],
		[Planes].[CompanyId],
		[Planes].[IsDeleted],
		[Planes].[CreatedDate],
		[Planes].[LastUpdate]
		
FROM
		[dbo].[Planes]
		INNER JOIN
		[dbo].[Company]
ON
		([Planes].[IsDeleted] = @IsDeleted) AND ([Company].[Id]=[Planes].[CompanyId]) AND
		((dbo.UTF8TOASCII([Planes].[PlaneId]) LIKE '%' + dbo.UTF8TOASCII(@SearchText) + '%') 
		OR (dbo.UTF8TOASCII([Planes].[PlaneName]) LIKE '%' + dbo.UTF8TOASCII(@SearchText) + '%')
		OR (dbo.UTF8TOASCII([Company].[Name]) LIKE '%' + dbo.UTF8TOASCII(@SearchText) + '%'))

GO


SET QUOTED_IDENTIFIER OFF 
GO


SET QUOTED_IDENTIFIER ON 
GO



CREATE PROCEDURE [dbo].mp_Planes_Insert

/*
Author:   			HungNM
Created: 			16-08-2012
Last Modified: 		16-08-2012
*/

@PlaneId varchar(20),
@PlaneName nvarchar(100),
@CompanyId bigint,
@IsDeleted bit,
@CreatedDate datetime,
@LastUpdate datetime

	
AS

INSERT INTO 	[dbo].[Planes] 
(
				[PlaneId],
				[PlaneName],
				[CompanyId],
				[IsDeleted],
				[CreatedDate],
				[LastUpdate]
) 

VALUES 
(
				@PlaneId,
				@PlaneName,
				@CompanyId,
				@IsDeleted,
				@CreatedDate,
				@LastUpdate
				
)
SELECT @@IDENTITY 


GO

SET QUOTED_IDENTIFIER OFF 
GO



SET QUOTED_IDENTIFIER ON 
GO



CREATE PROCEDURE [dbo].mp_Planes_Update

/*
Author:   			HungNM
Created: 			16-08-2012
Last Modified: 		16-08-2012
*/
	
@Id bigint, 
@PlaneId varchar(20), 
@PlaneName nvarchar(100), 
@CompanyId bigint, 
@IsDeleted bit, 
@CreatedDate datetime, 
@LastUpdate datetime 


AS

UPDATE 		[dbo].[Planes] 

SET
			[PlaneId] = @PlaneId,
			[PlaneName] = @PlaneName,
			[CompanyId] = @CompanyId,
			[IsDeleted] = @IsDeleted,
			[CreatedDate] = @CreatedDate,
			[LastUpdate] = @LastUpdate
			
WHERE
			[Id] = @Id

GO

SET QUOTED_IDENTIFIER OFF 
GO


/****** Object:  Stored Procedure [dbo].mp_Planes_SelectPage   Script Date: Thursday, August 16, 2012 ******/
if exists (select * from [dbo].sysobjects where id = object_id(N'[dbo].[mp_Planes_SelectPage]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[mp_Planes_SelectPage]
GO



CREATE PROCEDURE [dbo].mp_Planes_SelectPage

-- Author:   			HungNM
-- Created: 			16-08-2012
-- Last Modified: 		16-08-2012

@PageNumber 			int,
@PageSize 			int

AS

DECLARE @PageLowerBound int
DECLARE @PageUpperBound int


SET @PageLowerBound = (@PageSize * @PageNumber) - @PageSize
SET @PageUpperBound = @PageLowerBound + @PageSize + 1

/*
Note: temp tables use the server default for collation not the database default
so if adding character columns be sure and specify to use the database collation like this
to avoid collation errors:

CREATE TABLE #PageIndexForUsers
(
IndexID int IDENTITY (1, 1) NOT NULL,
UserName nvarchar(50) COLLATE DATABASE_DEFAULT,
LoginName nvarchar(50) COLLATE DATABASE_DEFAULT
) 


*/

DECLARE @PageIndex TABLE 
(
	IndexID int IDENTITY (1, 1) NOT NULL,
    Id BigInt
)

BEGIN

INSERT INTO @PageIndex ( 
    Id
)

SELECT
		[Id]
		
FROM
		[dbo].[Planes]
		
-- WHERE

-- ORDER BY

END


SELECT
		t1.*
		
FROM
		[dbo].[Planes] t1

JOIN			@PageIndex t2
ON			
		t1.[Id] = t2.[Id]
		
WHERE
		t2.IndexID > @PageLowerBound 
		AND t2.IndexID < @PageUpperBound
		
ORDER BY t2.IndexID

-- DROP TABLE #PageIndex

GO


SET QUOTED_IDENTIFIER OFF 
GO







